# -*- coding: utf-8 -*-
"""
与sybaseIQ通信的python常用函数
"""

import pypyodbc as pyodbc


def connect_sybase(dsn):
    """使用odbc数据源连接，需要指定dsn"""
    conn_string = "DSN=" + dsn
    conn = pyodbc.connect(conn_string)
    return conn


def df_into_sybase(conn, df, table):
    """将df批量导入数据库"""
    # 如果是空的数据框，什么都不做
    if len(df) == 0:
        return None

    cursor = conn.cursor()
    # 先将df全部转成字符串格式，主要是针对数值，df中的时间此时肯定是字符串类型
    df2 = df.applymap(lambda s: str(s))
    # 处理空值问题，因为刚才转成字符串时，None被转成"None"
    nan_df = df.notnull()
    df2 = df2.where(nan_df, '')  # 对于原来是NaN的，要转成空字符串

    # 创建导数的SQL
    sql = "insert into table_name ( columns ) values ( num_? )"
    cols = list(df2.columns)
    columns = ','.join(cols)
    value_str = ["?" for i in range(len(cols))]
    value_str = ",".join(value_str)  # 得到'?,?,?...'的字符串
    sql = sql.replace('table_name', table)
    sql = sql.replace('columns', columns)
    sql = sql.replace('num_?', value_str)

    # 将df转成list-tuple格式，才能导入数据库
    param = []
    if len(df2) > 1:
        for i in range(len(df2)):
            param.append(tuple(df2.iloc[i]))  # 转成list_tuple格式
    if len(df2) == 1:
        param = [tuple(df2.iloc[0])]
    # 入库
    cursor.executemany(sql, param)
    conn.commit()
    cursor.close()


def main():
    pass

if __name__ == '__main__':
    main()
